﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar;
using DevComponents.AdvTree;
using System.Data.SqlClient;
using Tunnel_Cross_Section.menue_frm;
using Tunnel_Cross_Section.option_frm;
using Tunnel_Cross_Section.相关类;
using System.IO;
using Excel;
using System.Threading;
using TunnelClasses; 

namespace Tunnel_Cross_Section
{
    public partial class Mfrm : Office2007RibbonForm
    {
        public List<Projects> current_projects;//用来管理当前的工程量
        public Projects cur_prj;
        DataBase m_db = null;
        System.Resources.ResourceManager resources;//用来调用资源文件
        public CAD_Class cad;
        public Mfrm()
        {
            InitializeComponent();
            Control.CheckForIllegalCrossThreadCalls = false;
            panelEx1.Style.BackColor1.Color = Color.LightBlue;
            panelEx1.Style.BackColor2.Color = Color.White;
            panelEx1.Style.GradientAngle = 45;
            resources = new System.Resources.ResourceManager("Tunnel_Cross_Section.pic", System.Reflection.Assembly.GetExecutingAssembly());
            Connect_To_DataBase();
            current_projects = new List<Projects>();//初始化程序管理List
            
        }


        //连接Sql数据库的函数,无脑型,不用管.
        private void Connect_To_DataBase()
        {
            DataBase db = null;
            string computer = System.Environment.MachineName;
            //if (DataBase.ConnectToDataBase("sqlserver", "",
            //         computer + "\\SQLEXPRESS", "", "", "cross_section", true, ref db))
            //{
            //    m_db = db;
            //}
            if (!DataBase.ConnectToDataBase("sqlserver", "192.168.5.48", "1433", "",
                     computer + "\\SQLEXPRESS", "sa", "123", "cross_section", false, ref db))
            {

                //if (MessageBox.Show("无法连接数据库\"cross_section\" ,是否要在本地创建新的数据库?", "Information", MessageBoxButtons.YesNo) == DialogResult.Yes)
                //{
                //    //string myExecuteQuery;
                //    //myExecuteQuery = "CREATE DATABASE cross_section ON PRIMARY " +
                //    //"(NAME = cross_section, " +
                //    //"FILENAME = 'C:\\cross_section.mdf', " +
                //    //"SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
                //    //"LOG ON (NAME = cross_section_log, " +
                //    //"FILENAME = 'C:\\cross_section.ldf', " +
                //    //"SIZE = 5MB, " +
                //    //"MAXSIZE = 200MB, " +
                //    //"FILEGROWTH = 10%)";

                //    //SqlConnection mySqlConnection = new SqlConnection("server=" + computer + "\\SQLEXPRESS;integrated security = true");
                //    //SqlCommand myCommand = new SqlCommand(myExecuteQuery, mySqlConnection);
                //    //myCommand.Connection.Open();
                //    //myCommand.ExecuteNonQuery();
                //    //mySqlConnection.Close();
                //    //bool c = DataBase.ConnectToDataBase("sqlserver", "",
                //    //    computer + "\\SQLEXPRESS", "", "", "cross_section", true, ref db);
                //    //m_db = db;

                //}
                //else
                //{

                //    return;
                //}
            }
            else
            {
                m_db = db;
            }
        }


        #region 新建工程
        private void btn_new_prj_Click(object sender, EventArgs e)
        {
            menue_frm.new_prj_from frm = new Tunnel_Cross_Section.menue_frm.new_prj_from(m_db,this);
            frm.ShowDialog();
            Label.Text = "开始新建区间";


        }
        #endregion

        #region 帮助信息
        private void btn_help_Click(object sender, EventArgs e)
        {
            option_frm.message_frm frm = new Tunnel_Cross_Section.option_frm.message_frm("你不会真指望我做个帮助文\r\n档吧!\r\n自己慢慢试!"
                ,"提示",0);
            frm.buttonX1.Visible = false;
            frm.ShowDialog();
            frm.Close();

        }
        #endregion 



        #region 
        //用来追踪现有的工程,并形成工程列表
        public void update_projects()
        {
            if (current_projects.Count == 0)
            {
                return;
            }
            tree_prj.Nodes[0].Nodes.Clear();
            for (int i = 0; i < current_projects.Count; i++)
            {
                DevComponents.AdvTree.Node new_node = new Node();
                new_node.Text = current_projects[i].section_name;
                new_node.Cells.Add(new Cell());
                new_node.Cells[1].Images.Image = ((System.Drawing.Image)(resources.GetObject("cancel")));
                tree_prj.Nodes[0].Nodes.Add(new_node);
                tree_prj.Nodes[0].Text = "当前工程(" + (tree_prj.Nodes.Count).ToString() + "个工程)";
            }
            tree_prj.Refresh();
        }
        

        #endregion

        #region 打开区间列表
        private void btn_open_Click(object sender, EventArgs e)
        {
            menue_frm.open_project_frm frm = new Tunnel_Cross_Section.menue_frm.open_project_frm(this, m_db);
            frm.MdiParent = this;
            frm.WindowState = FormWindowState.Maximized;
            frm.Show();
            frm.Update();
            Label.Text = "打开区间";
        }
        #endregion

        #region 删除某个工程
        private void tree_prj_NodeClick(object sender, TreeNodeMouseEventArgs e)
        {
            if (tree_prj.Nodes[0].Nodes.Count == 0)
            {
                return;
            }
            if (e.Button == MouseButtons.Left && e.Node.SelectedCell.Images.Image != null && e.Node.SelectedCell.Text == "")
            {
                int i = e.Node.Index;
                tree_prj.Nodes[0].Nodes.RemoveAt(i);

                tree_prj.Nodes[0].Text = "当前工程(" + tree_prj.Nodes[0].Nodes.Count.ToString() + "个工程)";
                Label.Text = "已关闭区间: " + current_projects[i].section_name;

                string frm_name = current_projects[i].section_name;
                for (int k = 0; k < this.MdiChildren.Length; k++)
                {
                    if (MdiChildren[k].Text.Contains(frm_name) == true)
                    {
                        MdiChildren[k].Close();
                    }
                }
                current_projects.RemoveAt(i);
                tree_prj.Refresh();

            }
        }
        #endregion


        #region 用户鼠标点击,切换活动工程
        private void tree_prj_AfterNodeSelect(object sender, AdvTreeNodeEventArgs e)
        {
            if (tree_prj.Nodes[0].Nodes.Count == 0)
            {
                return;
            }
            if (e.Node.Level == 1)
            {
                int i = e.Node.Index;
                cur_prj = current_projects[i];
                Label.Text = "已选择区间: " + cur_prj.section_name;
            }
        }
        #endregion

        private void btn_go_section_Click(object sender, EventArgs e)
        {

            if (current_projects.Count == 1)
            {
                cur_prj = current_projects[0];
            }
            else if (cur_prj == null)
            {
                option_frm.message_frm frm = new Tunnel_Cross_Section.option_frm.message_frm("请选择要进行操作的区间!"
                               , " 错误", 3);
                frm.ShowDialog();
                frm.Close();
                return;

            }
            do_section_frm frm1 = new do_section_frm(m_db, this);
            frm1.MdiParent = this;
            frm1.WindowState = FormWindowState.Maximized;
            if (frm1.no == 0)
            {
                frm1.Close();
                Label.Text = "收敛处理已取消";
                return;
            }
            frm1.Show();
           

        }

        #region 生成报告
        ApplicationClass excel;
        Worksheet sheet;
        Workbook m_Book;
        Range r3_range;
        Range r4_range;
        Range r5_range;
        Range r6_range;
        //点此,来生成报告
        private void buttonItem2_Click(object sender, EventArgs e)
        {
            Thread th = new Thread(new ThreadStart(generate_report));
            th.Start();

        }
        void generate_report()
        {
            this.UseWaitCursor = true;
            if (cur_prj == null)
            {
                message_frm aa = new message_frm("当前项目为空!", "错误", 0);
                aa.ShowDialog();
                aa.Close();
                return;
            }
            int index = tabStrip.SelectedTabIndex;
            if (index < 0)
            {
                message_frm aa = new message_frm("要在激活收敛处理\r\n的情况下才能生成报告!", "错误", 0);
                aa.ShowDialog();
                aa.Close();
                this.UseWaitCursor = false;
                return;
            }
            Form frm = this.MdiChildren[index];
            string type = frm.GetType().Name;
            if (type != "do_section_frm")
            {
                message_frm aa = new message_frm("要在激活收敛处理\r\n的情况下才能生成报告!", "错误", 0);
                aa.ShowDialog();
                aa.Close();
                this.UseWaitCursor = false;
                return;
            }
            object miss = Type.Missing;
            do_section_frm do_frm = frm as do_section_frm;
            string path = System.Windows.Forms.Application.StartupPath + "\\报表\\"
                + do_frm.Text + ".xls";
            //把这个excel命名成区间+期数的格式.
            try
            {
                if (File.Exists(path) == true)
                {
                    File.Delete(path);
                }
                File.Copy(System.Windows.Forms.Application.StartupPath + "\\template.xls", path);
            }
            catch (Exception a)
            {
                message_frm aa = new message_frm(a.Message, "出错", 0);
                aa.ShowDialog();
                aa.Close();
                this.UseWaitCursor = false;
                return;
            }
            Label.Text = "开始生成报表..请稍等..";
            
            bar1.Refresh();
            excel = new ApplicationClass();
            excel.Visible = true;
            m_Book = excel.Workbooks.Open(path, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
            sheet = m_Book.Sheets[1] as Worksheet;
            //sheet.Name = do_frm.Text;

            Worksheet sheet3 = m_Book.Sheets[3] as Worksheet;
            
            //sheet3.Name = "十字叉";
            
            r3_range = sheet3.Cells[1, 1] as Range;
            r4_range = sheet3.Cells[1, 6] as Range;
            r5_range = sheet3.Cells[1, 12] as Range;
            r6_range = sheet3.Cells[2, 12] as Range;
            //报告生成的时候不可视
            //excel.Visible = false;
            string table_name = "qj_" + do_frm.m_prj.id.ToString() + "_" + do_frm.m_prj.no.ToString();
            string name = "qj_" + do_frm.m_prj.id.ToString();
            string sql = "Select " + table_name + ".[断面号] from [" + table_name + "],[" + name + "] where " + name + ".[上下行]='L' and "
                    + table_name + ".[断面号]=" + name + ".[断面号] Group by " + name + ".[断面号]," + table_name + ".[断面号]";
            System.Data.DataTable dt;
            dt = m_db.GetDataSet(sql).Tables[0];
            List<string> L_names;//以此来储存上行线所有断面的断面号
            L_names = order_data(dt);
            sql = "Select " + table_name + ".[断面号] from [" + table_name + "],[" + name + "] where " + name + ".[上下行]='R' and "
                    + table_name + ".[断面号]=" + name + ".[断面号] Group by " + name + ".[断面号]," + table_name + ".[断面号]";
            dt = m_db.GetDataSet(sql).Tables[0];
            List<string> R_names = order_data(dt);//以此来储存下行线所有断面的断面号
            //获取了所有的上行和下行的名字;
            int max_row_count;
           
            if (R_names == null)
            {
                max_row_count = L_names.Count;
                progressBar.Maximum = L_names.Count;
            }
            else if (L_names == null)
            {
                max_row_count = R_names.Count;
                progressBar.Maximum = R_names.Count;
            }
            else if (R_names == null && L_names == null)
            {
                return;
            }
            else
            {
                if (L_names.Count > R_names.Count)
                {
                    max_row_count = L_names.Count;
                }
                else
                {
                    max_row_count = R_names.Count;
                }
                progressBar.Maximum = R_names.Count + L_names.Count;
                
            }

            max_row_count = max_row_count / 4;
            //获取要拷贝的区域
            Range r;
            r = sheet.Cells[3, 12] as Range;
            //区间名
            r.Value2 = "区间:" + do_frm.m_prj.section_name;
            r= sheet.get_Range(sheet.Cells[2, 1], sheet.Cells[63, 45]);

            for (int i = 0; i < max_row_count; i++)
            {
                Range des = sheet.Cells[64 + 62 * i, 1] as Range;
                //在这里把行数复制够!!
                //有了足够的行数就可以把数据复制进来了.
                r.Copy(des);
            }
            sheet.Rows.RowHeight = 15.75;
            m_Book.Save();
            //先处理下行线
            table_name = table_name + "_data";

            progressBar.Visible = true;//显示progressbar
            Label.Text = "正在生成报表...";
            if (R_names != null)
            {
                for (int i = 0; i < R_names.Count; i++)
                {

                    Get_Valfrom_SQL(R_names[i], table_name, i, do_frm.m_prj.id, do_frm.m_prj.no);
                }
            }
            if (L_names != null)
            {
                for (int i = 0; i < L_names.Count; i++)
                {
                    Get_Valfrom_SQL(L_names[i], table_name, i, do_frm.m_prj.id, do_frm.m_prj.no);
                }
            }
            progressBar.Visible = false;//隐藏progressbar
            progressBar.Value = 0;
            excel.Visible = true;
            this.UseWaitCursor = false;
            Label.Text = "报表生成完成!";
        }
        void Get_Valfrom_SQL(string name, string table, int index, int id, int no)
        {
            progressBar.PerformStep();
            string sql;
            System.Data.DataTable dt;
            string section_table = "qj_" + id.ToString();
            //这里把里程和沉降点号取出来
            sql = "select [里程],[沉降点号] from [" + section_table + "] where [断面号]='" + name + "'";
            dt = m_db.GetDataSet(sql).Tables[0];
            double lc;
            string cj_pt;
            if (dt.Rows.Count == 0)
            {
                lc = 0;
                cj_pt = "";
            }
            else
            {
                lc = Convert.ToDouble(dt.Rows[0][0]);
                cj_pt = dt.Rows[0][1].ToString();
            }
            

            sql = "select [变形] from [" + table + "] where [断面号]='" + name + "' order by [角度]";
            dt = m_db.GetDataSet(sql).Tables[0];
            System.Data.DataTable cr_dt;

            data_memeber_section dm = new data_memeber_section();
            //开始求本次变形
            if (no == 1)
            {
                cr_dt = dt;
            }
            else
            {
                string talbe_name = "qj_" + id.ToString() + "_" + (no - 1).ToString()+"_data";
                sql = "select [变形] from [" + talbe_name + "] where [断面号]='" + name + "' order by [角度]";
                cr_dt = m_db.GetDataSet(sql).Tables[0];

                for (int i = 0; i < 56; i++)
                {
                    try
                    {
                        cr_dt.Rows[i][0] = (Convert.ToDouble(dt.Rows[i][0]) - Convert.ToDouble(cr_dt.Rows[i][0]));
                    }

                    catch
                    {
                        MessageBox.Show("第" + name + "环计算问题");
                        break;
                    }

                }
            }
            
            //画图并填充改表格内所有的关联的数据
            if (dt.Rows.Count > 0 && cr_dt.Rows.Count > 0&&name.Contains("R"))
            {
                creat_Excel_graph(dt, cr_dt, index, lc, cj_pt, name);
            }
            else if (dt.Rows.Count > 0 && cr_dt.Rows.Count > 0)
            {
                creat_Excel_graph_L(dt, cr_dt, index, lc, cj_pt, name);
            }
            //Label.Text = "报表生成完成!";

        }

        void creat_Excel_graph(System.Data.DataTable ac_dt, System.Data.DataTable cr_dt,  int dex,double lc,string cj_pt,string LR_name)
        {

            int mod = dex % 4;///// 0,1,2,3
            int base_row = 4 + 62 * (dex / 4);
            int table_row = base_row + mod * 15;
            Object Missing = Type.Missing;
            Range r_ac;     
            Range r_cr;
            Range x_val;
            float top = (float)15.75 * (base_row + 3 + mod * 15);
            //本次累积上的断面号
            (sheet.Cells[base_row, 17 - 2 * mod] as Range).Value2 = LR_name;
            r_ac = sheet.Cells[base_row + 3, 18 - 2 * mod] as Range;
            r_cr = sheet.Cells[base_row + 3, 17 - 2 * mod] as Range;
            double radius_tmp = 0;
            double radius_tmp1 = 0;

            radius_tmp = (Convert.ToDouble(ac_dt.Rows[9][0]) + Convert.ToDouble(ac_dt.Rows[10][0]) + Convert.ToDouble(ac_dt.Rows[45][0]) +
                Convert.ToDouble(ac_dt.Rows[46][0])) / 2000;
            radius_tmp1 = (Convert.ToDouble(ac_dt.Rows[27][0]) + Convert.ToDouble(ac_dt.Rows[28][0])) / 2000;
            radius_tmp = Math.Round(radius_tmp, 3);
            radius_tmp1 = Math.Round(radius_tmp1, 3);
            if (Math.Abs(radius_tmp) > 0.05)
            {
                r4_range.Value2 = LR_name;
                r4_range.get_Offset(0, 1).Value2 = radius_tmp + 5.5;
                if (Math.Abs(radius_tmp1) > 0.05)
                {
                    r4_range.get_Offset(0, 2).Value2 = radius_tmp1 + 5.5;
                }
                else
                {
                    r4_range.get_Offset(0, 2).Value2 = "\\";
                }

                r4_range.get_Offset(0, 3).Value2 = lc;
                r4_range = r4_range.get_Offset(1, 0);
            }
            else if (Math.Abs(radius_tmp1) > 0.05)
            {
                r4_range.Value2 = LR_name;
                r4_range.get_Offset(0, 1).Value2 = "\\";
                r4_range.get_Offset(0, 2).Value2 = radius_tmp1 + 5.5;
                r4_range.get_Offset(0, 3).Value2 = lc;
                r4_range = r4_range.get_Offset(1, 0);
            }

            r3_range.Value2 = LR_name;
            r3_range.get_Offset(0, 1).Value2 = radius_tmp + 5.5;
            r3_range.get_Offset(0, 2).Value2 = radius_tmp1 + 5.5;
            r3_range.get_Offset(0, 3).NumberFormatLocal = "K" + "##" + "+" + "###" + "." + "#";
            r3_range.get_Offset(0, 3).Value2 = lc;
            r3_range = r3_range.get_Offset(1, 0);

            double max_zj = 0;
            double max_jd = 42.5;
            double min_zj = 999;
            double min_jd = 42.5;
            for (int i = 0; i < 36; i++)
            {
                
                double zj;
                if (i <= 19)
                {
                    zj = Convert.ToDouble(ac_dt.Rows[i][0]) / 1000 + Convert.ToDouble(ac_dt.Rows[i + 36][0]) / 1000 + 5.5;
                }
                else
                {
                    zj = Convert.ToDouble(ac_dt.Rows[i][0]) / 1000 + 5.5;
                }
                if (zj > max_zj)
                {
                    max_zj = zj;
                    max_jd = 42.5 + 5 * i;
                }
                if (zj < min_zj)
                {
                    min_zj = zj;
                    min_jd = 42.5 + +5 * i;
                }
 
            }
            if (r5_range.Value2 == null)
            {
                r5_range.Value2 = LR_name;
                r5_range.get_Offset(0, 1).Value2 = max_jd;
                r5_range.get_Offset(0, 2).Value2 = max_zj;
                r5_range.get_Offset(0, 3).Value2 = lc;
            }
            else
            {
                if (Convert.ToDouble(r5_range.get_Offset(0, 2).Value2) < max_zj)
                {
                    r5_range.Value2 = LR_name;
                    r5_range.get_Offset(0, 1).Value2 = max_jd;
                    r5_range.get_Offset(0, 2).Value2 = max_zj;
                    r5_range.get_Offset(0, 3).Value2 = lc;
                }
 
            }

            if (r5_range.get_Offset(0,4).Value2 == null)
            {
                r5_range.get_Offset(0, 4).Value2 = LR_name;
                r5_range.get_Offset(0, 5).Value2 = min_jd;
                r5_range.get_Offset(0, 6).Value2 = min_zj;
                r5_range.get_Offset(0, 7).Value2 = lc;
            }
            else
            {
                if (Convert.ToDouble(r5_range.get_Offset(0, 6).Value2) > min_zj)
                {
                    r5_range.get_Offset(0, 4).Value2 = LR_name;
                    r5_range.get_Offset(0, 5).Value2 = min_jd;
                    r5_range.get_Offset(0, 6).Value2 = min_zj;
                    r5_range.get_Offset(0, 7).Value2 = lc;
                }

            }


            //sheet.Activate();
            double max_angle = -9999;
            double max = 0, min = 0;
            double min_angle = 9999;
            double[] vals = new double[7];//特征点的变形
            //先填充本次变化数据和累计变化数据,并获得了最大最小半径
            for (int i = 0; i < 56; i++)
            {
                r_ac.Value2 = ac_dt.Rows[i][0].ToString();
                r_cr.Value2 = cr_dt.Rows[i][0].ToString();
                if ((Convert.ToDouble(cr_dt.Rows[i][0])) > 6)
                {
                    r_cr.Font.ColorIndex = 7;
                }
                else if ((Convert.ToDouble(cr_dt.Rows[i][0])) < -6)
                {
                    r_cr.Font.ColorIndex = 3;
                }
                r_ac = r_ac.get_Offset(1, 0);
                r_cr = r_cr.get_Offset(1, 0);
                if (Convert.ToDouble(cr_dt.Rows[i][0]) < min_angle)
                {
                    min_angle = Convert.ToDouble(cr_dt.Rows[i][0]);
                    min = 42.5 + i * 5;
                }
                if (Convert.ToDouble(cr_dt.Rows[i][0]) > max_angle)
                {
                    max_angle = Convert.ToDouble(cr_dt.Rows[i][0]);
                    max = 42.5 + i * 5;
                }
                if (i % 9 == 0)
                {
                    vals[i / 9] = (Convert.ToDouble(cr_dt.Rows[i][0]) + Convert.ToDouble(cr_dt.Rows[i + 1][0])) / 2;
                }
            }

            //设定数据区域,开始添加图表
            r_ac = sheet.get_Range(sheet.Cells[base_row + 3, 18 - 2 * mod], sheet.Cells[base_row + 58, 18 - 2 * mod]);
            r_cr = sheet.get_Range(sheet.Cells[base_row + 3, 17 - 2 * mod], sheet.Cells[base_row + 58, 17 - 2 * mod]);
            x_val = sheet.get_Range(sheet.Cells[base_row + 3, 19], sheet.Cells[base_row + 58, 19]);
            m_Book.Charts.Add(Missing, Missing, 1, Missing);
            m_Book.ActiveChart.ChartType = Excel.XlChartType.xlXYScatterLines;//xlColumnClustered;//设置图形
            m_Book.ActiveChart.SetSourceData(sheet.get_Range(sheet.Cells[7, 17], sheet.Cells[62, 19]), XlRowCol.xlColumns);
            Chart aa = m_Book.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, sheet.Name);
            aa.ChartArea.AutoScaleFont = false;
            string name = aa.Name;
            int index = aa.Name.IndexOf(' ');
            name = aa.Name.Substring(index + 1);
            aa = (sheet.ChartObjects(name) as ChartObject).Chart;
            
            Series s1 = aa.SeriesCollection(1) as Series;
            s1.Name = "累计变化";
            //把数据取到对应的单元格
            (aa.SeriesCollection(1) as Series).Values = r_ac;
            (aa.SeriesCollection(1) as Series).XValues = x_val;
            s1.MarkerSize = 3;
            //把数据取到对应的单元格
            Series s2 = aa.SeriesCollection(2) as Series;
            s2.Name = "本次变化";
            s2.Values = r_cr;
            s2.XValues = x_val;
            s2.MarkerBackgroundColorIndex = XlColorIndex.xlColorIndexNone;
            s2.MarkerStyle = XlMarkerStyle.xlMarkerStyleX;
            aa.PlotArea.Interior.ColorIndex = 2;
            aa.PlotArea.Interior.PatternColorIndex = 3;
            aa.PlotArea.Border.ColorIndex = 57;
            s2.MarkerSize = 5;
            Axis ax = (aa.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary) as Axis);
            ax.Crosses = XlAxisCrosses.xlAxisCrossesMaximum;
            ax.MaximumScale = 80;
            ax.MinimumScale = -80;
            ax.MajorUnit = 20;
            ax.MinorUnit = 10;
            ax.TickLabels.AutoScaleFont = false;
            //ax.TickLabels.Font.Size = 24;
            ax = (aa.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary) as Axis);
            //ax.TickLabels.Font.Size = 24;
            //给图表定位和固定大小
            Shape sh = sheet.Shapes.Item(name);
            sh.Width = 438.75F; 
            sh.Height = 157.5F;
            sh.Left = 0;
            sh.Top = top + 2.75F;
            aa.PlotArea.Width = 354;
            aa.Legend.Left = 354;
            aa.Legend.Width = 65;
            aa.Legend.Height = 26;
            aa.Legend.Top = 68;
            aa.Legend.Font.Size = 8;
 
            //放断面号,最小最大半径等等
            Range table_range = sheet.Cells[table_row, 2] as Range;
            //断面号
            table_range.Value2 = LR_name;
            //里程
            //table_range.get_Offset(1, 0).NumberFormatLocal = "XK" + "##+###.#";
            table_range.get_Offset(1, 0).Value2 = lc;//"XK" + get_lc(lc);
            //table_range.get_Offset(1, 0).NumberFormatLocal = "XK###+###.#";
            //沉降点号
            table_range.get_Offset(0, 1).Value2 = "("+cj_pt+")";
            //最大半径
            table_range = sheet.Cells[table_row, 6] as Range;
            table_range.Value2 = max_angle / 1000 + 2.75;
            //最大半径角度
            table_range.get_Offset(0, 1).Value2 = "/" + max.ToString();
            //最小半径
            table_range.get_Offset(1, 0).Value2 = min_angle / 1000 + 2.75;
            //最小半径角度
            table_range.get_Offset(1, 1).Value2 = "/" + min.ToString();
            //椭圆度
            table_range.get_Offset(0, 3).Value2 = Math.Round((max_angle / 1000 + 2.75) / (min_angle / 1000 + 2.75), 4);
            //7个特征变形量
            table_range = sheet.Cells[table_row + 3, 2] as Range;
            for (int i = 0; i < vals.Length; i++)
            {
                table_range.get_Offset(0, i).Value2 = vals[i];
            }
            m_Book.Save();

        }
        void creat_Excel_graph_L(System.Data.DataTable ac_dt, System.Data.DataTable cr_dt, int dex, double lc, string cj_pt, string LR_name)
        {

            int mod = dex % 4;///// 0,1,2,3
            int base_row = 4 + 62 * (dex / 4);
            int table_row = base_row + mod * 15;
            Object Missing = Type.Missing;
            Range r_ac;
            Range r_cr;
            float top = (float)15.75 * (base_row + 3 + mod * 15);
            //本次累积上的断面号
            (sheet.Cells[base_row, 20 + 2 * mod] as Range).Value2 = LR_name;
            r_ac = sheet.Cells[base_row + 3, 21 + 2 * mod] as Range;
            r_cr = sheet.Cells[base_row + 3, 20 + 2 * mod] as Range;
            double max_angle = -9999;
            double max = 0, min = 0;
            double min_angle = 9999;
            double[] vals = new double[7];//特征点的变形
            
            double radius_tmp = 0;
            double radius_tmp1 = 0;
            radius_tmp = (Convert.ToDouble(ac_dt.Rows[9][0]) + Convert.ToDouble(ac_dt.Rows[10][0]) + Convert.ToDouble(ac_dt.Rows[45][0]) +
                Convert.ToDouble(ac_dt.Rows[46][0])) / 2000;
            radius_tmp1 = (Convert.ToDouble(ac_dt.Rows[27][0]) + Convert.ToDouble(ac_dt.Rows[28][0])) / 2000;
            radius_tmp = Math.Round(radius_tmp, 3);
            radius_tmp1 = Math.Round(radius_tmp1, 3);
            if (Math.Abs(radius_tmp) > 0.05)
            {
                r4_range.Value2 = LR_name;
                r4_range.get_Offset(0, 1).Value2 = radius_tmp + 5.5;
                if (Math.Abs(radius_tmp1) > 0.05)
                {
                    r4_range.get_Offset(0, 2).Value2 = radius_tmp1 + 5.5;
                }
                else
                {
                    r4_range.get_Offset(0, 2).Value2 = "\\";
                }

                r4_range.get_Offset(0, 3).Value2 = lc;
                r4_range = r4_range.get_Offset(1, 0);
            }
            else if (Math.Abs(radius_tmp1) > 0.05)
            {
                r4_range.Value2 = LR_name;
                r4_range.get_Offset(0, 1).Value2 = "\\";
                r4_range.get_Offset(0, 2).Value2 = radius_tmp1 + 5.5;
                r4_range.get_Offset(0, 3).Value2 = lc;
                r4_range = r4_range.get_Offset(1, 0);
            }
            r3_range.Value2 = LR_name;
            r3_range.get_Offset(0, 1).Value2 = radius_tmp + 5.5;
            r3_range.get_Offset(0, 2).Value2 = radius_tmp1 + 5.5;
            r3_range.get_Offset(0, 3).NumberFormatLocal = "K" + "##" + "+" + "###" + "." + "#";
            r3_range.get_Offset(0, 3).Value2 = lc;
            r3_range = r3_range.get_Offset(1, 0);

            double max_zj = 0;
            double max_jd = 42.5;
            double min_zj = 999;
            double min_jd = 42.5;
            for (int i = 0; i < 36; i++)
            {

                double zj;
                if (i <= 19)
                {
                    zj = Convert.ToDouble(ac_dt.Rows[i][0]) / 1000 + Convert.ToDouble(ac_dt.Rows[i + 36][0]) / 1000 + 5.5;
                }
                else
                {
                    zj = Convert.ToDouble(ac_dt.Rows[i][0]) / 1000 + 5.5;
                }
                if (zj > max_zj)
                {
                    max_zj = zj;
                    max_jd = 42.5 + 5 * i;
                }
                if (zj < min_zj)
                {
                    min_zj = zj;
                    min_jd = 42.5 + +5 * i;
                }

            }
            if (r6_range.Value2 == null)
            {
                r6_range.Value2 = LR_name;
                r6_range.get_Offset(0, 1).Value2 = max_jd;
                r6_range.get_Offset(0, 2).Value2 = max_zj;
                r6_range.get_Offset(0, 3).Value2 = lc;
            }
            else
            {
                if (Convert.ToDouble(r6_range.get_Offset(0, 2).Value2) < max_zj)
                {
                    r6_range.Value2 = LR_name;
                    r6_range.get_Offset(0, 1).Value2 = max_jd;
                    r6_range.get_Offset(0, 2).Value2 = max_zj;
                    r6_range.get_Offset(0, 3).Value2 = lc;
                }

            }

            if (r6_range.get_Offset(0, 4).Value2 == null)
            {
                r6_range.get_Offset(0, 4).Value2 = LR_name;
                r6_range.get_Offset(0, 5).Value2 = min_jd;
                r6_range.get_Offset(0, 6).Value2 = min_zj;
                r6_range.get_Offset(0, 7).Value2 = lc;
            }
            else
            {
                if (Convert.ToDouble(r6_range.get_Offset(0, 6).Value2) > min_zj)
                {
                    r6_range.get_Offset(0, 4).Value2 = LR_name;
                    r6_range.get_Offset(0, 5).Value2 = min_jd;
                    r6_range.get_Offset(0, 6).Value2 = min_zj;
                    r6_range.get_Offset(0, 7).Value2 = lc;
                }

            }


            //sheet.Activate();
            //先填充本次变化数据和累计变化数据,并获得了最大最小半径
            for (int i = 0; i < 56; i++)
            {
                r_ac.Value2 = ac_dt.Rows[i][0];
                r_cr.Value2 = cr_dt.Rows[i][0];
                //if (Math.Abs(Convert.ToDouble(ac_dt.Rows[i][0])) > 10)
                //{
                //    r_ac.Font.ColorIndex = 3;
                //}
                if ((Convert.ToDouble(cr_dt.Rows[i][0])) > 6)
                {
                    r_cr.Font.ColorIndex = 7;
                }
                else if ((Convert.ToDouble(cr_dt.Rows[i][0])) < -6)
                {
                    r_cr.Font.ColorIndex = 3;
                }
                r_ac = r_ac.get_Offset(1, 0);
                r_cr = r_cr.get_Offset(1, 0);
                if (Convert.ToDouble(cr_dt.Rows[i][0]) < min_angle)
                {
                    min_angle = Convert.ToDouble(cr_dt.Rows[i][0]);
                    min = 42.5 + i * 5;
                }
                if (Convert.ToDouble(cr_dt.Rows[i][0]) > max_angle)
                {
                    max_angle = Convert.ToDouble(cr_dt.Rows[i][0]);
                    max = 42.5 + i * 5;
                }
                if (i % 9 == 0)
                {
                    vals[i / 9] = (Convert.ToDouble(cr_dt.Rows[i][0]) + Convert.ToDouble(cr_dt.Rows[i + 1][0])) / 2;
                }
            }

            //设定数据区域,开始添加图表
            r_ac = sheet.get_Range(sheet.Cells[base_row + 3, 21 + 2 * mod], sheet.Cells[base_row + 58, 21 + 2 * mod]);
            r_cr = sheet.get_Range(sheet.Cells[base_row + 3, 20 + 2 * mod], sheet.Cells[base_row + 58, 20 + 2 * mod]);

            Range x_val = sheet.get_Range(sheet.Cells[base_row + 3, 19], sheet.Cells[base_row + 58, 19]);
            m_Book.Charts.Add(Missing, Missing, 1, Missing);
            m_Book.ActiveChart.ChartType = Excel.XlChartType.xlXYScatterLines;//xlColumnClustered;//设置图形
            m_Book.ActiveChart.SetSourceData(sheet.get_Range(sheet.Cells[7, 19], sheet.Cells[62, 21]), XlRowCol.xlColumns);
            Chart aa = m_Book.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, sheet.Name);
            aa.ChartArea.AutoScaleFont = false;
            string name = aa.Name;
            int index = aa.Name.IndexOf(' ');
            name = aa.Name.Substring(index + 1);
            aa = (sheet.ChartObjects(name) as ChartObject).Chart;
            Series s1 = aa.SeriesCollection(1) as Series;
            s1.Name = "累计变化";
            //把数据取到对应的单元格
            (aa.SeriesCollection(1) as Series).Values = r_ac;
            (aa.SeriesCollection(1) as Series).XValues = x_val;
            s1.MarkerSize = 3;
            //把数据取到对应的单元格
            Series s2 = aa.SeriesCollection(2) as Series;
            s2.Name = "本次变化";
            s2.Values = r_cr;
            s2.XValues = x_val;
            s2.MarkerBackgroundColorIndex = XlColorIndex.xlColorIndexNone;
            s2.MarkerStyle = XlMarkerStyle.xlMarkerStyleX;
            aa.PlotArea.Interior.ColorIndex = 2;
            aa.PlotArea.Interior.PatternColorIndex = 3;
            aa.PlotArea.Border.ColorIndex = 57;
            s2.MarkerSize = 5;
     
            Axis ax = (aa.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary) as Axis);
            ax.Crosses = XlAxisCrosses.xlAxisCrossesMaximum;
            ax.MaximumScale = 80;
            ax.MinimumScale = -80;
            ax.MajorUnit = 20;
            ax.MinorUnit = 10;
            //ax.TickLabels.Font.Size = 24;
            ax = (aa.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary) as Axis);
            //ax.TickLabels.Font.Size = 24;
            //给图表定位和固定大小
            Shape sh = sheet.Shapes.Item(name);
            sh.Width = 438.75F;
            sh.Height = 157.5F;
            sh.Left = 1093.25F;
            sh.Top = top + 2.75F;
            aa.PlotArea.Width = 354;
            aa.Legend.Left = 354;
            aa.Legend.Width = 65;
            aa.Legend.Height = 26;
            aa.Legend.Top = 68;
            aa.Legend.Font.Size = 8;
            //放断面号,最小最大半径等等
            Range table_range = sheet.Cells[table_row, 30] as Range;
            //断面号
            table_range.Value2 = LR_name;
            //里程
            
            table_range.get_Offset(1, 0).Value2 = lc;//"SK" + get_lc(lc);
           // NumberFormatLocal = """SK""##""+""###"".""#"
            
            //沉降点号
            table_range.get_Offset(0, 1).Value2 = "(" + cj_pt + ")";
            //最大半径
            table_range = sheet.Cells[table_row, 34] as Range;
            table_range.Value2 = max_angle / 1000 + 2.75;
            //最大半径角度
            table_range.get_Offset(0, 1).Value2 = "/" + max.ToString();
            //最小半径
            table_range.get_Offset(1, 0).Value2 = min_angle / 1000 + 2.75;
            //最小半径角度
            table_range.get_Offset(1, 1).Value2 = "/" + min.ToString();
            //椭圆度
            table_range.get_Offset(0, 3).Value2 = Math.Round((max_angle / 1000 + 2.75) / (min_angle / 1000 + 2.75), 4);
            //7个特征变形量
            table_range = sheet.Cells[table_row + 3, 30] as Range;
            for (int i = 0; i < vals.Length; i++)
            {
                table_range.get_Offset(0, i).Value2 = vals[i];
            }
            m_Book.Save();
        }

        List<string> order_data(System.Data.DataTable dt)
        {
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            List<string> sts = new List<string>();
            List<int> str = new List<int>();
            string LR = dt.Rows[0][0].ToString()[0].ToString();
            try
            {

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string id = dt.Rows[i][0].ToString();
                    int int_id = Convert.ToInt32(id.Substring(1));
                    str.Add(int_id);
                }
                str.Sort();
                for (int i = 0; i < str.Count; i++)
                {
                    sts.Add(LR + expand_id(str[i].ToString()));
                }

            }
            catch (Exception e)
            {
                message_frm frm = new message_frm(e.Message, "异常", 3);
                frm.ShowDialog();
                frm.Close();
                return null;
            }
            return sts;
        }
        string get_lc(double lc)
        {
            int a =(int) (lc / 1000);
            double b = Math.Round(lc - a * 1000, 2);
            return a.ToString() + "+" + b.ToString();
        }
        string expand_id(string name)
        {
            if (name.Length == 3)
            {
                return name;
            }
            else if (name.Length == 2)
            {
                return "0" + name;
            }
            else
            {
                return "00" + name;
            }

        }
        #endregion

        #region 窗体结束,注销cad变量
        private void Mfrm_FormClosed(object sender, FormClosedEventArgs e)
        {
            if (cad != null)
            {
                cad.Dispose();
            }
        }
        #endregion
    }
}